package com.example.expresshelp.db;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.os.Environment;

import com.example.expresshelp.util.Constance;
import com.example.expresshelp.util.MyConfig;

/**
 * 数据库帮助类
 */
public class MySqliteHelper extends SQLiteOpenHelper {

	// 缓存数据库操作类的单例
	private static volatile MySqliteHelper instance;
	// 数据库名和版本号
	private final static String DB_NAME = "express.db";
	private final static int DB_VERSION = 1;
	private Context context;
	// 数据库对象
	private SQLiteDatabase db;
	private String path = Environment.getExternalStorageDirectory() + "/";

	/**
	 * 对外公开得到实例的方法
	 * 
	 * @param context
	 *            上下文
	 * @return 操作缓存数据库的实例
	 */
	public static MySqliteHelper getInstance(Context context) {

		if (instance == null) {
			synchronized (MySqliteHelper.class) {
				if (instance == null) {
					instance = new MySqliteHelper(context);
				}
			}
		}
		return instance;
	}

	/**
	 * 默认的构造方法
	 * 
	 * @param context
	 */
	public MySqliteHelper(Context context) {
		super(context, DB_NAME, null, DB_VERSION);
		try {
			this.context = context;
			// 打开或创建数据库
			db = SQLiteDatabase.openOrCreateDatabase(path + DB_NAME, null);
			if (!MyConfig.getDbCreated(context)) {
				//createDataTable(db);
				MyConfig.setDbCreated(context);
			}
		} catch (Exception e) {
			e.printStackTrace();
			db = this.getWritableDatabase();
		}

	}

	/**
	 * 创建表
	 * 
	 * @param db
	 */
	private void createDataTable(SQLiteDatabase db) {
		if (db == null) {
			return;
		}
		try {
			createPrivinceTable(db);
			createCityTable(db);
			createDistrictTable(db);
			createTowntrTable(db);
			createVillagetrTable(db);
		} catch (Exception e) {
			e.printStackTrace();
		}

	}

	/**
	 * 创建省份表
	 */
	private void createPrivinceTable(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + Constance.PROVINCE_TABLE_NMAE);
		db.execSQL("CREATE TABLE IF NOT EXISTS "
				+ Constance.PROVINCE_TABLE_NMAE
				+ " (id integer primary key autoincrement, code varchar, name varchar)");
	}

	/**
	 * 创建城市表
	 */
	private void createCityTable(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + Constance.CITY_TABLE_NMAE);
		db.execSQL("CREATE TABLE IF NOT EXISTS "
				+ Constance.CITY_TABLE_NMAE
				+ " (id integer primary key autoincrement, pcode varchar, code varchar, name varchar)");
	}

	/**
	 * 创建区表
	 */
	private void createDistrictTable(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + Constance.DISTRICT_TABLE_NMAE);
		db.execSQL("CREATE TABLE IF NOT EXISTS "
				+ Constance.DISTRICT_TABLE_NMAE
				+ " (id integer primary key autoincrement, ccode varchar, code varchar, name varchar)");
	}

	/**
	 * 创建镇表
	 */
	private void createTowntrTable(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + Constance.TOWNTR_TABLE_NMAE);
		db.execSQL("CREATE TABLE IF NOT EXISTS "
				+ Constance.TOWNTR_TABLE_NMAE
				+ " (id integer primary key autoincrement, dcode varchar, code varchar, name varchar)");
	}

	/**
	 * 创建村表
	 */
	private void createVillagetrTable(SQLiteDatabase db) {
		db.execSQL("DROP TABLE IF EXISTS " + Constance.VILLAGETR_TABLE_NMAE);
		db.execSQL("CREATE TABLE IF NOT EXISTS "
				+ Constance.VILLAGETR_TABLE_NMAE
				+ " (id integer primary key autoincrement, tcode varchar, code varchar, name varchar)");
	}

	/**
	 * 生命周期-创建方法
	 */
	@Override
	public void onCreate(SQLiteDatabase db) {
		createDataTable(db);
	}

	/**
	 * 生命周期-更新方法
	 */
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		createDataTable(db);
	}

	/**
	 * 插入
	 */
	public synchronized long insert(String tableName, String columnName,
			ContentValues values) {
		try {
			return db.insert(tableName, columnName, values);
		} catch (Exception e) {
			e.printStackTrace();
		}
		return -1;
	}

	/**
	 * 多个插入
	 */
	public void insert(String tableName, String columnName,
			ContentValues[] values) {
		try {
			for (int i = 0; i < values.length; i++) {
				insert(tableName, columnName, values[i]);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 查询方法
	 * 
	 * @param tableName
	 * @param where
	 * @return
	 */
	public synchronized Cursor query(String tableName, String where) {
		String result = null;

		String sql = "select * from " + tableName + " where " + where;
		if (db != null) {
			Cursor cursor = db.rawQuery(sql, null);
			return cursor;
		}
		return null;
	}

	/**
	 * 从数据库中取出缓存的json数据
	 * 
	 * @param key
	 * @return value-json缓存
	 */
	public synchronized String getCacheData(String key) {
		if (key == null)
			return null;
		String result = null;
		String sql = "select *from datacache where _key = '" + key + "'";
		if (db != null) {
			Cursor cursor = db.rawQuery(sql, null);
			while (cursor.moveToNext()) {
				// 得到数据库中json缓存
				result = cursor.getString(1);
				break;
			}
			cursor.close();
		}
		return result;
	}

	/**
	 * 清除缓存，即删除缓存表，再重新建立一个
	 */
	public synchronized void clearCache(String tableName) {
		if (db == null) {
			return;
		}
		try {
			db.execSQL("drop table " + tableName);
			createDataTable(db);
		} catch (Exception e) {
			// TODO: handle exception
		}
	}

	/**
	 * 关闭资源
	 * 
	 * @see android.database.sqlite.SQLiteOpenHelper#close()
	 */
	@Override
	public synchronized void close() {
		super.close();
		if (db != null) {
			db.close();
		}
	}

}
